package com.yc.jdbc;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.Properties;

// 常用的命名: DBUtils, JdbcTemplate
// 基于静态方法实现
public class DBHelper {
	

	
	/**
	 * 查询列表(0~n行记录)
	 * @param sql
	 * @param args
	 * @return
	 */
	public static List<Map<String,Object>> selectList(String sql, Object...args) {
		Function<PreparedStatement,List<Map<String,Object>>> fn = new Function<>() {
			
			@Override
			public List<Map<String, Object>> apply(PreparedStatement t) {
				try {
					List<Map<String, Object>> list = new ArrayList<>();
					ResultSet rs = t.executeQuery();
					// 结果集元数据对象
					ResultSetMetaData meta = rs.getMetaData();
					// 获取当前结果集字段数
					int count = meta.getColumnCount();
					while(rs.next()) {
						Map<String, Object> row = new LinkedHashMap<>();
						for (int i = 0; i < count; i++) {
							// 获取该行所有的字段值, 写入该Map集合中
							String columnName = meta.getColumnName(i+1);
							Object columnValue = rs.getObject(columnName);
							row.put(columnName, columnValue);
						}
						list.add(row);
					}
					return list;
				} catch (SQLException e) {
					throw new RuntimeException("执行查询语句失败!", e);
				}
				
			}
		};
		return execute(sql, fn, args);
	}
	
	/**
	 * 查询单行(0~1行记录), 如果结果数量大于1, 则表示错误
	 * @param sql
	 * @param args
	 * @return
	 */
	public static Map<String,Object> selectOne(String sql, Object...args) {
		Function<PreparedStatement,Map<String,Object>> fn = new Function<>() {
			
			@Override
			public Map<String, Object> apply(PreparedStatement t) {
				try {
					ResultSet rs = t.executeQuery();
					ResultSetMetaData meta = rs.getMetaData();
					Map<String, Object> row = null;
					if(rs.next()) {
						row = new LinkedHashMap<>();
						// 获取当前结果集字段数
						int count = meta.getColumnCount();
						for (int i = 0; i < count; i++) {
							// 获取该行所有的字段值, 写入该Map集合中
							String columnName = meta.getColumnName(i+1);
							Object columnValue = rs.getObject(columnName);
							row.put(columnName, columnValue);
						}
						if(rs.next()) {
							throw new RuntimeException("查询结果数量大于1!");
						}
					}
					return row;
				} catch (SQLException e) {
					throw new RuntimeException("执行查询语句失败!", e);
				}
				
			}
		};
		return execute(sql, fn, args);
	}
	
	public static void main(String[] args) { 
		
		init();
		
		Object ar[] = {2000, 8001};
		DBHelper.update("update emp set comm = ? where empno = ?", 1000, 8001);
		DBHelper.update("update emp set sal = ? where empno = ?", ar); // 效果一样
		System.out.println("----------------------------------------");
		List<?> list =  selectList("select * from emp where ename like ?", "%A%");
		// list集合遍历 + lambda表达式
		list.forEach(System.out::println);
		System.out.println("----------------------------------------");
		list =  selectList("select * from emp where sal > ?", 1000);
		// list集合遍历 + lambda表达式
		list.forEach(System.out::println);
		System.out.println("----------------------------------------");
		Map<?,?> emp = selectOne("select * from emp where empno = ?" , 7839);
		System.out.println(emp);
		System.out.println("----------------------------------------");
		try {
			emp = selectOne("select * from emp where job = ?" , "MANAGER");
			System.out.println(emp);
		}catch(Exception e) {
			System.out.println(e.getMessage());
		}
		System.out.println("----------------------------------------");
		System.out.println("count: "+ count("select * from emp"));
		System.out.println("----------------------------------------");
		System.out.println("--------------全部-------------");
		PageDTO pd = selectPage("select * from emp where sal>?", 1, 100, 1000);
		System.out.println("总行数: " + pd.getTotal());
		pd.records.forEach(System.out::println);
		
		System.out.println("--------------第一页-------------");
		pd = selectPage("select * from emp where sal>?", 1, 5, 1000);
		System.out.println("总行数: " + pd.getTotal());
		pd.records.forEach(System.out::println);
		
		System.out.println("--------------第二页-------------");
		pd = selectPage("select * from emp where sal>?", 2, 5, 1000);
		System.out.println("总行数: " + pd.getTotal());
		pd.records.forEach(System.out::println);
		
	}
	
	/**
	 * 统计结果的行数, 其它的平局值, 最大最小...
	 * @param sql
	 * @param args
	 * @return
	 */
	public static int count(String sql, Object...args) {
		String countSql = String.format("select count(*) cnt from (%s)", sql);
		Map<String,Object> row = selectOne(countSql, args);
		Object cnt = row.get("CNT");
		int ret = Integer.parseInt(cnt.toString());
		return ret;
	}
	
	/**
	 * 分页查询
	 * @param sql
	 * @param page 第几页, 从1开始
	 * @param size 每页的最大行数
	 * @param args
	 */
	public static PageDTO selectPage(String sql, int page, int size, Object...args) {
		String recordSql = "select *\n" + 
						"  from (select a.*, rownum rn from (%s) a where rownum <= %s) a\n" + 
						" where a.rn > %s";
		int beginRow = size * (page - 1);
		int endRow = size * page;
		recordSql = String.format(recordSql, sql, endRow, beginRow);
		List<Map<String,Object>> records = selectList(recordSql, args);
		int total = count(sql,args);
		PageDTO pageDto = new PageDTO();
		pageDto.setTotal(total);
		pageDto.setRecords(records);
		return pageDto;
	}
	
	// DTO => Data To Object
	public static class PageDTO{
		List<Map<String,Object>> records; // 当前页数据
		int total;  // 总行数  => 计算出总页数
		
		public List<Map<String, Object>> getRecords() {
			return records;
		}
		public void setRecords(List<Map<String, Object>> records) {
			this.records = records;
		}
		public int getTotal() {
			return total;
		}
		public void setTotal(int total) {
			this.total = total;
		}
	}
	
	/** Object...args 可变数组参数 => 
	 * 1.是一个数组, 
	 * 2.必须定义在方法参数列表的最后一个参数位置
	 * 3.调用方法传参, 非常方便, 传参个数随意
	 * 4.如果传入的是一个数组, 参数会自动拆分出来
	 * @param sql
	 * @param args
	 * @return
	 */
	public static int update(String sql, Object...args) {
		// 匿名内部类 => 转成lambda
		Function<PreparedStatement, Integer> function = new Function<>(){
			@Override
			public Integer apply(PreparedStatement t) {
				try {
					return t.executeUpdate();
				} catch (SQLException e) {
					throw new RuntimeException("执行增删改语句错误!", e);
				}
			}
		};
		execute(sql, function, args);
		return 0;
	}
	
	////////////////////////////////////////////////////////////
	private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
	private static String username = "scott";
	private static String password = "a";
	
	private static <O> O execute(String sql, Function<PreparedStatement,O> function, Object...args) {
//		Connection conn = null;
		// jdk 1.7 try 的语法的写法优化了, try(定义的对象,必须是自动可关闭对象AutoCloseable,
		// try在结束之后,会自动关闭)
		try(Connection conn = DriverManager.getConnection(url, username, password)) {
			// 构建语句
			PreparedStatement ps = conn.prepareStatement(sql);
			// 设置参数
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i+1, args[i]);
			}
			// 打印，方便后续调试
			System.out.println("SQL: " + sql);
			System.out.println("参数: " + Arrays.toString(args));
			
			// 执行语句， 处理结果 TODO
			return function.apply(ps);
		} catch (SQLException e) {
			throw new RuntimeException("SQL执行错误!", e);
		}
		
	}
	
	public static void init() {
		// Properties 集合 =》 Map集合
		Properties props = new Properties();
		// FileInputStream 文件流  当前场景不适用
		// 类加载器读取类路径中的文件
		ClassLoader loader = DBHelper.class.getClassLoader();
		try(InputStream in = loader.getResourceAsStream("jdbc.properties");) {
			// 读取配置到 Properties 文件中
			props.load(in);	
			String driver = props.getProperty("driver");
			url = props.getProperty("url");
			username = props.getProperty("username");
			password = props.getProperty("password");
			Class.forName(driver);
			System.out.println("数据库配置读取成功！");
			System.out.printf("driver: %s \n", driver);
			System.out.printf("url: %s \n", url);
			System.out.printf("username: %s \n", username);
			System.out.printf("password: %s \n", password);
		}catch(Exception e) {
			throw new RuntimeException("数据库配置初始化失败！", e);
		}
		
	}
	
	
	

}
